from collections import Counter

import pandas as pd
import pymysql
from pandas import DataFrame
from pyecharts.commons.utils import JsCode
from pyecharts.charts import Bar, Line, Boxplot, WordCloud
from pyecharts import options as opts
from pyecharts.globals import ThemeType


def views(df):
    # 设置显示所有列
    # pd.set_option('display.max_columns', None)
    # # 设置显示所有行
    # pd.set_option('display.max_rows', None)
    # 数据预处理
    # 找出观看次数最多的前十个视频
    top10_videos = df.nlargest(10, 'view_counts')
    top10_videos.sort_values(by='view_counts', inplace=True)

    print(top10_videos)
    # 创建条形图
    bar = Bar(init_opts=opts.InitOpts(width='1600px', height='800px'))
    bar.add_xaxis(top10_videos['title'].tolist())
    bar.add_yaxis("观看次数", top10_videos['view_counts'].tolist(), label_opts=opts.LabelOpts(position="right"))
    bar.set_global_opts(
        title_opts=opts.TitleOpts(title="观看次数最多的前十个视频"),
        xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=45, interval=0)),
        yaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=55, interval=0)),
        tooltip_opts=opts.TooltipOpts(trigger="axis", axis_pointer_type="cross")
    )
    bar.reversal_axis()
    bar.render("观看次数最多的前十个视频.html")

    data = df['video_type'].dropna()
    # 获取所有关键字并计算频率
    keywords = ' '.join(data).split('/')
    keyword_counts = Counter(keywords)
    print(keyword_counts)

    # 生成词云图
    wordcloud_keywords = (
        WordCloud(init_opts=opts.InitOpts(width='1300px', height='700px'))
        .add("", list(keyword_counts.items()), word_size_range=[20, 100])
        .set_global_opts(title_opts=opts.TitleOpts(title="视频类型关键字词云"))
    )
    wordcloud_keywords.render('视频类型关键字词云.html')

    data: DataFrame = df
    data['video_type'] = df['video_type'].fillna('').apply(lambda x: x.split('/')[0])
    data['month'] = df['video_time'].dt.to_period('M')

    # 计算每个月每个类型的视频播放量总和
    monthly_type_views = data.groupby(['month', 'video_type'])['view_counts'].sum().reset_index()
    # 找出每个月播放量最高的类型
    monthly_top_types: DataFrame = monthly_type_views.loc[monthly_type_views.groupby('month')['view_counts'].idxmax()]
    print(monthly_top_types)

    data_list = []
    for i in monthly_top_types.iterrows():
        data_list.append(str(i[1][0]) + ' ' + str(i[1][1]) + '区')

    # 创建柱状图
    bar = (
        Bar(init_opts=opts.InitOpts(width='1400px', height='700px', theme=ThemeType.ESSOS))
        .add_xaxis(data_list)
        .add_yaxis('播放量', monthly_top_types['view_counts'].tolist())
        .set_global_opts(
            title_opts=opts.TitleOpts(title="每月播放量最高的视频分区"),
            xaxis_opts=opts.AxisOpts(type_="category", axislabel_opts=opts.LabelOpts(rotate=30)),
            yaxis_opts=opts.AxisOpts(type_="value"),
            tooltip_opts=opts.TooltipOpts(trigger="axis"),
            datazoom_opts=[opts.DataZoomOpts(type_="slider", pos_bottom="0%")],
        )
    )

    bar.render('每月播放量最高的视频分区.html')

    # 不同视频类型的观看次数
    boxplot = Boxplot(init_opts=opts.InitOpts(width='1400px', height='700px', theme=ThemeType.LIGHT))
    video_types = df['video_type'].unique().tolist()
    data = [df[df['video_type'] == vt]['view_counts'].tolist() for vt in video_types]
    print(data)
    # 过滤掉空列表和长度不足的数据
    filtered_data = [d for d in data if len(d) >= 5]
    filtered_video_types = [video_types[i] for i in range(len(video_types)) if len(data[i]) >= 5]
    print(filtered_video_types)
    if not filtered_data:
        print("No sufficient data for boxplot.")
        return

    boxplot.add_xaxis(filtered_video_types)
    boxplot.add_yaxis("观看次数", boxplot.prepare_data(filtered_data),
                      itemstyle_opts=opts.ItemStyleOpts(color="#c23531"))
    boxplot.set_global_opts(
        title_opts=opts.TitleOpts(title="不同视频分区的观看次数",
                                  title_textstyle_opts=opts.TextStyleOpts(font_size=20)),
        xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=45, font_size=12)),
        yaxis_opts=opts.AxisOpts(name="观看次数", name_textstyle_opts=opts.TextStyleOpts(font_size=14)),
        tooltip_opts=opts.TooltipOpts(trigger="axis", axis_pointer_type="cross"),
        legend_opts=opts.LegendOpts(is_show=True, pos_top="5%", pos_right="5%")
    )
    boxplot.render("不同视频分区的观看次数.html")

    # 提取年份
    df['year'] = pd.to_datetime(df['video_time']).dt.year
    # 统计每个name每年must_see大于10次的情况
    result = df.groupby(['name', 'year']).size().reset_index(name='count')
    result = result[result['count'] > 6]
    print(result)

    # 创建柱状图
    color_func = """
        function(params){
            if (params.value > 0 && params.value < 10){
                return 'gray';
            }
            else if (params.value >= 10 && params.value < 20){
                return 'black';
            }
            else {
                return 'red';
            }
        }
    """

    bar = Bar(init_opts=opts.InitOpts(width='1400px', height='700px', theme=ThemeType.LIGHT))

    # 按年份分组并添加数据
    for name in result['name'].unique():
        name_data = result[result['name'] == name]
        bar.add_yaxis(name, name_data['count'].tolist(), itemstyle_opts=opts.ItemStyleOpts(color=JsCode(color_func)))

    bar.add_xaxis(result['year'].unique().tolist())
    bar.set_global_opts(
        title_opts=opts.TitleOpts(title="每年每周必看大于六次的UP主统计"),
        datazoom_opts=opts.DataZoomOpts(),  # 添加滑块
        # 调整小标签位置并设置为滚动模式
        legend_opts=opts.LegendOpts(pos_top="5%", pos_left="center", orient="horizontal", type_="scroll"),
        tooltip_opts=opts.TooltipOpts(trigger="axis")
    )
    bar.render("每年每周必看大于六次的UP主统计.html")

    # 提取小时
    df['hour'] = df['video_time'].dt.hour

    # 按小时统计播放量
    hour_view_counts = df.groupby('hour')['view_counts'].sum().sort_index()
    print(hour_view_counts)

    # 创建折线图
    line = (
        Line(init_opts=opts.InitOpts(width='1400px', height='700px', theme=ThemeType.LIGHT))
        .add_xaxis(hour_view_counts.index.astype(str).tolist())
        .add_yaxis("播放量", hour_view_counts.values.tolist(), is_smooth=True,
                   linestyle_opts=opts.LineStyleOpts(width=2))
        .set_global_opts(
            title_opts=opts.TitleOpts(title="统计那个时间段发布视频最受欢迎", subtitle="数据来源：Bilibili"),
            xaxis_opts=opts.AxisOpts(name="小时", type_="category", boundary_gap=False),
            yaxis_opts=opts.AxisOpts(name="播放量"),
            tooltip_opts=opts.TooltipOpts(trigger="axis"),
            toolbox_opts=opts.ToolboxOpts(is_show=True),
            visualmap_opts=opts.VisualMapOpts(is_show=True, max_=100000000, min_=500000000)
        )
    )

    # 渲染图表
    line.render("统计那个时间段发布视频最受欢迎.html")


def read_data_from_mysql():
    # 创建数据库连接
    connection = pymysql.connect(host='localhost', user='root', password='123456', database='bilibili')

    # 执行查询
    query = "SELECT * FROM video_data"
    df = pd.read_sql(query, con=connection)

    # 关闭连接
    connection.close()

    return df


if __name__ == '__main__':
    df = read_data_from_mysql()
    views(df)
